package com.yzyz.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.yzyz.bean.scenic.ScenicBean;
import com.yzyz.dao.IScenicDao;
import com.yzyz.utils.ValidateUtils;
import com.yzyz.utils.mapper.ScenicRowMapper;
/**
 * 
 * @author David Sun
 * @Date   2010-3-15
 *
 */
public class ScenicDaoImpl implements IScenicDao {
	private JdbcTemplate jdbcTemplate;
	@SuppressWarnings("unchecked")
	@Override
	public ScenicBean get(final String name) {
		String sql = "select * from scenic where name=?";
		List<ScenicBean> atts = this.jdbcTemplate.query(sql, new PreparedStatementSetter() {
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, name);
			}
		}, new ScenicRowMapper());
		if(atts == null || atts.size() != 1) {
			return null;
		}
		return atts.get(0);
	}

	@Override
	public int delete(int id) {
		String sql = "delete from scenic where id=" + id;
		return this.jdbcTemplate.update(sql);

	}

	@SuppressWarnings("unchecked")
	@Override
	public Object get(int id) {
		String sql = "select * from scenic where id=" + id;
		List<ScenicBean> atts = this.jdbcTemplate.query(sql, new ScenicRowMapper());
		if(atts == null || atts.size() != 1) {
			return null;
		}
		return atts.get(0);
	}

	@Override
	public int save(Object object) {
		final ScenicBean at = (ScenicBean)object;
		final String sql = "insert into scenic (name,available, ownerId, location,date) values (?,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		this.jdbcTemplate.update(new PreparedStatementCreator() {
			public java.sql.PreparedStatement createPreparedStatement(
					Connection conn) throws SQLException {
				java.sql.PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, at.getName());
				ps.setBoolean(2, at.isAvailable());
				ps.setInt(3, at.getOwnerId());
				ps.setString(4, at.getLocaton());
				ps.setTimestamp(5, new java.sql.Timestamp(at.getDate().getTime()));
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().intValue();
	}

	@Override
	public void update(Object object) {
		final ScenicBean att = (ScenicBean) object;
		String sql = "update scenic set name=?,location=?, ownerId=?,level=? where id=?";
		this.jdbcTemplate.update(sql, new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, att.getName());
				ps.setString(2, att.getLocaton());
				ps.setInt(3, att.getOwnerId());
				ps.setInt(4, att.getLevel());
				ps.setInt(5, att.getId());
			}});
	}

	@Override
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	public int updateAvailable(final int id, final boolean available) {
		String sql = "update scenic set available=? where id=?";
		return this.jdbcTemplate.update(sql, new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setBoolean(1, available);
				ps.setInt(2, id);
			}});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ScenicBean> getAllByRegion(int ownerId, int start, int limit) {
		String sql = "select * from scenic where ownerId=" + ownerId 
		+ " and available=1 order by level desc limit " + start + " , " + limit;
		List<ScenicBean> list = this.jdbcTemplate.query(sql, new ScenicRowMapper());
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ScenicBean> getAllWithoutDisabled() {
		String sql = "select * from scenic where available=1";
		List<ScenicBean> list = this.jdbcTemplate.query(sql, new ScenicRowMapper());
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ScenicBean> getAllWithoutDisabled(int ownerId) {
		String sql = "select * from scenic where ownerId="+ownerId+" and available=1";
		return this.jdbcTemplate.query(sql, new ScenicRowMapper());
	}

	@Override
	public int getCountAll(String name, int groupId) {
		String sql = "select count(*) from scenic where 1=1 ";
		if(!ValidateUtils.isNull(name)) {
			sql = sql + " and name like '%" + name + "%'";
		} 
		if(groupId > 0) {
			sql = sql + " and ownerId=" + groupId;
		}
		return this.jdbcTemplate.queryForInt(sql);
	}

	@Override
	public int getCountWithoutDisabled() {
		String sql = "select count(*) from scenic where available=1";
		return this.jdbcTemplate.queryForInt(sql);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ScenicBean> paginate(int start, int limit, String name, int groupId) {
		String sql = "select * from scenic where 1=1";
		if(!ValidateUtils.isNull(name)) {
			sql = sql + " and name like '%" + name + "%'";
		} 
		if(groupId > 0) {
			sql = sql + " and ownerId=" + groupId;
		}
		sql = sql + " limit " + start + ", " + limit;
		return this.jdbcTemplate.query(sql, new ScenicRowMapper());
	}

	@Override
	public int getCount(int ownerId) {
		String sql = "select count(scenic.*) from scenic, region_scenic  where region_scenic.regionId=" + ownerId 
		+ " and scenic.id=reion_scenic.scenicId and available=1";
		return this.jdbcTemplate.queryForInt(sql);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ScenicBean> getUsed(int routeId) {
		String sql = "select * from scenic, route_scenic where route_scenic.routeId=" + routeId + " and scenic.id=route_scenic.scenicId and scenic.available=true";
		return this.jdbcTemplate.query(sql, new ScenicRowMapper());
	}

	@Override
	public int getCountByRegion(int regionId) {
		String sql = "select count(*) from scenic where ownerId=" + regionId;
		return this.jdbcTemplate.queryForInt(sql);
	}

	@Override
	public void addUsedScenic(int routeId, int scenicId) {
		String sql = "insert into route_scenic(routeId, scenicId)values("+routeId+","+scenicId+")";
		this.jdbcTemplate.update(sql);
		
	}

	@Override
	public void deleteUsedScenic(int routeId, int scenicId) {
		String sql = "delete from route_scenic where routeId="+routeId + " and scenicId=" + scenicId;
		this.jdbcTemplate.execute(sql);
		
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ScenicBean> search(String keyWords) {
		String sql = "select * from scenic where name like '%" + keyWords + "%'";
		return this.jdbcTemplate.query(sql, new ScenicRowMapper());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ScenicBean> getAllByRoute(int routeId) {
		String sql = "select scenic.* from scenic, route_scenic where route_scenic.routeId=" + routeId 
		+ " and scenic.id=route_scenic.scenicId and scenic.available=1";
		return this.jdbcTemplate.query(sql, new RowMapper(){

			@Override
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				ScenicBean att = new ScenicBean();
				att.setAvailable(rs.getBoolean("scenic.available"));
				att.setDate(new Date(rs.getTimestamp("scenic.date").getTime()));
				att.setId(rs.getInt("scenic.id"));
				att.setLocaton(rs.getString("scenic.location"));
				att.setName(rs.getString("scenic.name"));
				att.setOwnerId(rs.getInt("scenic.ownerId"));
				att.setLevel(rs.getInt("scenic.level"));
				return att;
			}});
	}

}
